from string import Template


class FKAnalyzer:
    def __init__(self):
        self.fk_prompt = Template(
            """
            You are a database expert analyzing potential foreign key relationships between tables.
            Based on the table structures and sample values, determine if there is a foreign key relationship.

            Table 1: $table1
            Column 1: $col1
            Sample values from $table1.$col1: $samples1

            Table 2: $table2
            Column 2: $col2
            Sample values from $table2.$col2: $samples2

            Additional context about the tables:
            $table1_description: $table1_desc
            $table2_description: $table2_desc

            Please analyze if $table1.$col1 is likely a foreign key referencing $table2.$col2.
            Consider:
            1. Column naming conventions
            2. Data type compatibility
            3. Value overlap and cardinality
            4. Business logic and domain knowledge
            5. Table descriptions and relationships

            Respond with a JSON object in the format:
            {
              "is_foreign_key": true/false,
              "confidence": 0.0-1.0,
              "relationship_type": "one-to-one/one-to-many/many-to-many",
              "rationale": "brief explanation of your reasoning"
            }
            """
        )

    def build_fk_analysis_prompt(self, table1, col1, samples1, table1_desc,
                                 table2, col2, samples2, table2_desc):
        return self.fk_prompt.substitute(
            table1=table1,
            col1=col1,
            samples1=str(list(samples1)[:10]) if samples1 else "No sample data available",
            table2=table2,
            col2=col2,
            samples2=str(list(samples2)[:10]) if samples2 else "No sample data available",
            table1_description=table1,
            table1_desc=table1_desc,
            table2_description=table2,
            table2_desc=table2_desc
        )


# 使用示例
fk_analyzer = FKAnalyzer()

# 对于每个潜在的外键关系，构建提示
potential_fks = [
    {
        "table1": "cif_amend",
        "col1": "CLIENT_NO",
        "samples1": {"C001", "C002", "C003"},  # 假设的样本数据
        "table1_desc": "业务信息变更操作记录表",
        "table2": "cif_client",
        "col2": "CLIENT_NO",
        "samples2": {"C001", "C002", "C003", "C004"},
        "table2_desc": "客户信息主表"
    },
    # 添加更多潜在的外键关系...
]

for fk in potential_fks:
    prompt = fk_analyzer.build_fk_analysis_prompt(
        fk["table1"], fk["col1"], fk["samples1"], fk["table1_desc"],
        fk["table2"], fk["col2"], fk["samples2"], fk["table2_desc"]
    )
    print(prompt)
    print("\n" + "=" * 80 + "\n")

    # 这里可以添加调用DeepSeek API的代码
    # response = call_deepseek_api(prompt)
    # 解析响应并记录结果